package com.bw.gmallbi.mapper;


import com.bw.gmallbi.bean.ProtalStats;
import org.apache.ibatis.annotations.Select;

import java.util.List;


/**
 * 项目规划及管理
 * 上海大数据学院院长 ：孙丰朝
 * 技术指导及需求分析：郭洵
 * 编程：楚志高
 *
 * @author bawei  bigdata sh
 * @since 2021-06-11
 */

public interface ProtalPersonMapper {

    //获取按年龄段进行销售额度的比重分析
    //下面的统计是单一的维度的进行统计

    // 得到的是基于订单的统计
    @Select("SELECT yearbasetype,yearbasename, sum(split_total_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY  yearbasetype, yearbasename HAVING total_amount > 0 ORDER BY total_amount DESC ")
    List<ProtalStats> getTotalamountByYea();


    @Select(" SELECT user_gendertype,  user_gender, sum(split_total_amount) AS total_amount  FROM protrait_order\n  " +
            "  GROUP BY   user_gendertype, user_gender HAVING total_amount > 0 ORDER BY total_amount DESC ")
    List<ProtalStats> getTotalamountBySex();

    @Select(" select province_name , sum(split_total_amount) as total_amount  from protrait_order   " +
            "  group by  province_name  ")
    List<ProtalStats> getTotalamountByArea();
    @Select(" SELECT carrier,  carriername,  sum(split_total_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY   carrier,  carriername HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getTotalamountByCarry();
    @Select("  SELECT  email,  sum(split_total_amount) AS total_amount FROM protrait_order " +
            "  GROUP BY email HAVING total_amount > 0 ORDER BY total_amount DESC LIMIT 100 ")
    List<ProtalStats> getTotalamountByEmal();


    @Select(" select category1_id , category1_name ,sum(split_total_amount) as total_amount     " +
            "  from protrait_order group by  category1_id ,category1_name having total_amount >0  order by total_amount desc  limit 10   ")
    List<ProtalStats> getTotalamountByCate1();
    @Select("  select category2_id , category2_name ,sum(split_total_amount) as total_amount    from protrait_order group by  category2_id ,category2_name  " +
            " having total_amount >0  order by total_amount desc  limit 10   ")
    List<ProtalStats> getTotalamountByCate2();
    @Select(" select category3_id , category3_name ,sum(split_total_amount) as total_amount      from protrait_order group by  category3_id ,category3_name   " +
            "  having total_amount >0  order by total_amount desc  ")
    List<ProtalStats> getTotalamountByCate3();
    @Select("  SELECT  tm_name,  sum(split_total_amount) AS total_amount FROM protrait_order  " +
            " GROUP BY tm_name HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getTotalamountByTmname();
    //下面的维度统计是按2个维度进行统计
    @Select("  select user_gender , yearbasename,  sum(split_total_amount)  as total_amount from protrait_order  group by yearbasename , user_gender  " +
            " order by total_amount desc   ")
    List<ProtalStats> getTotalamountByYearSex();
    @Select(" SELECT yearbasetype,yearbasename, province_name, sum(split_total_amount) AS total_amount   " +
            " FROM protrait_order GROUP BY  yearbasetype, yearbasename, province_name  HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getTotalamountByYearArea();
    @Select(" SELECT carriername,yearbasename, sum(split_total_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY   yearbasename ,carriername HAVING total_amount > 0 ORDER BY  carriername,total_amount DESC   ")
    List<ProtalStats> getTotalamountByCarryYear();
    @Select("  SELECT carriername,province_name, sum(split_total_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY   carriername ,province_name HAVING total_amount > 0 ORDER BY  carriername,total_amount DESC  ")
    List<ProtalStats> getTotalamountByCarryArea();
    @Select("select  carriername,  user_gender ,sum(split_total_amount)  as total_amount from protrait_order  group by carriername , user_gender     " +
            " order by carriername, total_amount desc   ")
    List<ProtalStats> getTotalamountByCarrySex();
    //品牌对人性的带货
    @Select("  SELECT tm_name, user_gender, sum(split_total_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY   tm_name ,user_gender HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getTotalamountByTmnameSex();
    @Select(" SELECT tm_name,yearbasename, sum(split_total_amount) AS total_amount   " +
            " FROM protrait_order GROUP BY   tm_name ,yearbasename HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getTotalamountByTmnameYear();
    @Select("  SELECT tm_name, province_name, sum(split_total_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY   tm_name ,province_name HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getTotalamountByTmnameArea();
    //人性3个维度的组合
    @Select("  SELECT province_name, yearbasename, user_gender,  sum(split_total_amount) AS total_amount   " +
            "  FROM protrait_order GROUP BY  yearbasename, user_gender, province_name HAVING total_amount > 0 ORDER BY  province_name,total_amount DESC  ")
    List<ProtalStats> getTotalamountByYearSexArea();

    //////////////////////////////////////////////////////////////////
    //首单减免与折扣减免的用户画像只与人性有关,与运营商，与品牌可能绑定
    @Select("SELECT  yearbasename,  sum(coupon_reduce_amount) AS total_amount FROM protrait_order    " +
            "  GROUP BY yearbasename HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getCouponByYea();
    @Select("  SELECT  user_gender,  sum(coupon_reduce_amount) AS total_amount FROM protrait_order  " +
            " GROUP BY user_gender HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getCouponBySex();
    @Select("  SELECT  province_name,  sum(coupon_reduce_amount) AS total_amount FROM protrait_order  " +
            " GROUP BY province_name HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getCouponByArea();
    @Select(" SELECT  carriername,  sum(coupon_reduce_amount) AS total_amount FROM protrait_order   " +
            " GROUP BY carriername HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getCouponByCarry();
    @Select(" SELECT  tm_name,  sum(coupon_reduce_amount) AS total_amount FROM protrait_order   " +
            "  GROUP BY tm_name HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getCouponByTmname();
    @Select("  SELECT user_gender, yearbasename,  sum(coupon_reduce_amount) AS total_amount   " +
            " FROM protrait_order GROUP BY  yearbasename, user_gender   HAVING total_amount > 0 ORDER BY  user_gender,total_amount DESC   ")
    List<ProtalStats> getCouponByYearSex();
    @Select("  SELECT province_name, yearbasename,  sum(coupon_reduce_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY  yearbasename, province_name   HAVING total_amount > 0 ORDER BY  province_name,total_amount DESC   ")
    List<ProtalStats> getCouponByYearArea();
    @Select("  SELECT province_name, user_gender,  sum(coupon_reduce_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY  user_gender, province_name   HAVING total_amount > 0 ORDER BY  province_name,total_amount DESC   ")
    List<ProtalStats> getCouponBySexArea();
    @Select(" SELECT province_name, yearbasename , user_gender,  sum(coupon_reduce_amount) AS total_amount    " +
            "  FROM protrait_order GROUP BY   yearbasename ,user_gender, province_name   HAVING total_amount > 0 ORDER BY  province_name,total_amount DESC  ")
    List<ProtalStats> getCouponByYearSexArea();
    //系统按电商的分类级别进行统计用户画像
    @Select("  SELECT  category1_name,  sum(coupon_reduce_amount) AS total_amount FROM protrait_order  " +
            "   GROUP BY category1_name HAVING total_amount > 0 ORDER BY total_amount DESC ")
    List<ProtalStats> getCouponByByCate1();
    @Select(" SELECT  category2_name,  sum(coupon_reduce_amount) AS total_amount FROM protrait_order  " +
            "  GROUP BY category2_name HAVING total_amount > 0 ORDER BY total_amount DESC ")
    List<ProtalStats> getCouponByByCate2();
    @Select("  SELECT  category3_name,  sum(coupon_reduce_amount) AS total_amount FROM protrait_order  " +
            " GROUP BY category3_name HAVING total_amount > 0 ORDER BY total_amount DESC   ")
    List<ProtalStats> getCouponByByCate3();
    //折扣看品牌与人性结合进行减免可能减免
    @Select(" SELECT tm_name, yearbasename , sum(coupon_reduce_amount) AS total_amount   " +
            " FROM protrait_order GROUP BY   tm_name ,yearbasename   HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getCouponByTmnameYea();
    @Select(" SELECT tm_name, user_gender , sum(coupon_reduce_amount) AS total_amount   " +
            " FROM protrait_order GROUP BY   tm_name ,user_gender   HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getCouponByTmnamSex();
    @Select("  SELECT tm_name, province_name , sum(coupon_reduce_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY   tm_name ,province_name   HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getCouponByTmnameArea();

    /////////////////////////////////////////////////////////
    // 折扣针对人性，电商配合平台分类，品牌针对人性进行折扣活动

    @Select("SELECT  yearbasename,  sum(activity_reduce_amount) AS total_amount FROM protrait_order    " +
            "  GROUP BY yearbasename HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getActivtyByYea();
    @Select("  SELECT  user_gender,  sum(activity_reduce_amount) AS total_amount FROM protrait_order  " +
            " GROUP BY user_gender HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getActivtyBySex();
    @Select("  SELECT  province_name,  sum(activity_reduce_amount) AS total_amount FROM protrait_order  " +
            " GROUP BY province_name HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getActivtyByArea();
    @Select(" SELECT  carriername,  sum(activity_reduce_amount) AS total_amount FROM protrait_order   " +
            " GROUP BY carriername HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getActivtyByCarry();
    @Select(" SELECT  tm_name,  sum(activity_reduce_amount) AS total_amount FROM protrait_order   " +
            "  GROUP BY tm_name HAVING total_amount > 0 ORDER BY total_amount DESC  ")
    List<ProtalStats> getActivtyByTmname();
    @Select("  SELECT user_gender, yearbasename,  sum(activity_reduce_amount) AS total_amount   " +
            " FROM protrait_order GROUP BY  yearbasename, user_gender   HAVING total_amount > 0 ORDER BY  user_gender,total_amount DESC   ")
    List<ProtalStats> getActivtyByYearSex();
    @Select("  SELECT province_name, yearbasename,  sum(activity_reduce_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY  yearbasename, province_name   HAVING total_amount > 0 ORDER BY  province_name,total_amount DESC   ")
    List<ProtalStats> getActivtyByYearArea();
    @Select("  SELECT province_name, user_gender,  sum(activity_reduce_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY  user_gender, province_name   HAVING total_amount > 0 ORDER BY  province_name,total_amount DESC   ")
    List<ProtalStats> getActivtyBySexArea();
    @Select(" SELECT province_name, yearbasename , user_gender,  sum(activity_reduce_amount) AS total_amount    " +
            "  FROM protrait_order GROUP BY   yearbasename ,user_gender, province_name   HAVING total_amount > 0 ORDER BY  province_name,total_amount DESC  ")
    List<ProtalStats> getActivtyByYearSexArea();
    //系统按电商的分类级别进行统计用户画像
    @Select("  SELECT  category1_name,  sum(activity_reduce_amount) AS total_amount FROM protrait_order  " +
            "   GROUP BY category1_name HAVING total_amount > 0 ORDER BY total_amount DESC ")
    List<ProtalStats> getActivtyByByCate1();
    @Select(" SELECT  category2_name,  sum(activity_reduce_amount) AS total_amount FROM protrait_order  " +
            "  GROUP BY category2_name HAVING total_amount > 0 ORDER BY total_amount DESC ")
    List<ProtalStats> getActivtyByByCate2();
    @Select("  SELECT  category3_name,  sum(activity_reduce_amount) AS total_amount FROM protrait_order  " +
            " GROUP BY category3_name HAVING total_amount > 0 ORDER BY total_amount DESC   ")
    List<ProtalStats> getActivtyByByCate3();
    //折扣看品牌与人性结合进行减免可能减免
    @Select(" SELECT tm_name, yearbasename , sum(activity_reduce_amount) AS total_amount   " +
            " FROM protrait_order GROUP BY   tm_name ,yearbasename   HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getActivtyByTmnameYea();
    @Select(" SELECT tm_name, user_gender , sum(activity_reduce_amount) AS total_amount   " +
            " FROM protrait_order GROUP BY   tm_name ,user_gender   HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getActivtyByTmnamSex();
    @Select("  SELECT tm_name, province_name , sum(activity_reduce_amount) AS total_amount  " +
            " FROM protrait_order GROUP BY   tm_name ,province_name   HAVING total_amount > 0 ORDER BY  tm_name,total_amount DESC  ")
    List<ProtalStats> getActivtyByTmnameArea();
}